package org.zjx.db;

import org.zjx.common.bean.Role;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class RoleDB {
    private static boolean isTableCreated = false;  // Flag to ensure the table is only created once
    private final SQLiteHelper dbHelper;

    public RoleDB() {
        dbHelper = SQLiteHelper.getSingleton();
        // Only create the table once when the instance is first created
        if (!isTableCreated) {
            createTable();
            isTableCreated = true;
        }
    }

    // Create table for Role
    public void createTable() {
        String createTable = "CREATE TABLE IF NOT EXISTS role (\n"
                + "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
                + "    countId INTEGER NOT NULL,\n"
                + "    serverId INTEGER NOT NULL,\n"
                + "    roleName TEXT,\n"
                + "    roleId TEXT NOT NULL,\n"
                + "    raceId INTEGER NOT NULL,\n"
                + "    sects INTEGER NOT NULL,\n"
                + "    level INTEGER NOT NULL,\n"
                + "    vitality INTEGER NOT NULL,\n"
                + "    physical INTEGER NOT NULL,\n"
                + "    popularity INTEGER NOT NULL,\n"
                + "    gangName TEXT,\n"
                + "    gangNumber INTEGER NOT NULL,\n"
                + "    gangValue INTEGER NOT NULL,\n"
                + "    sectValue INTEGER NOT NULL,\n"
                + "    potential INTEGER NOT NULL,\n"
                + "    experience INTEGER NOT NULL,\n"
                + "    maxHp INTEGER NOT NULL,\n"
                + "    maxMp INTEGER NOT NULL,\n"
                + "    rage INTEGER NOT NULL,\n"
                + "    cash INTEGER NOT NULL,\n"
                + "    fund INTEGER NOT NULL,\n"
                + "    deposit INTEGER NOT NULL,\n"
                + "    maxPetValue INTEGER NOT NULL,\n"
                + "    status INTEGER NOT NULL\n"
                + ");";
        dbHelper.createTable(createTable);
    }

    // Insert a Role record
    public void insert(Role role) {
        String sql = "INSERT INTO role(countId, serverId, roleName, roleId, raceId, sects, level, vitality, physical, popularity, "
                + "gangName, gangNumber, gangValue, sectValue, potential, experience, maxHp, maxMp, rage, cash, fund, deposit, "
                + "maxPetValue, status) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Object[] params = {
                role.getCountId(),
                role.getServerId(),
                role.getRoleName(),
                role.getRoleId(),
                role.getRaceId(),
                role.getSects(),
                role.getLevel(),
                role.getVitality(),
                role.getPhysical(),
                role.getPopularity(),
                role.getGangName(),
                role.getGangNumber(),
                role.getGangValue(),
                role.getSectValue(),
                role.getPotential(),
                role.getExperience(),
                role.getMaxHp(),
                role.getMaxMp(),
                role.getRage(),
                role.getCash(),
                role.getFund(),
                role.getDeposit(),
                role.getMaxPetValue(),
                role.getStatus()
        };
        dbHelper.executeUpdate(sql, params);
    }

    // Update a Role record
    public void update(Role role) {
        String sql = "UPDATE role SET countId = ?, serverId = ?, roleName = ?, roleId = ?, raceId = ?, sects = ?, level = ?, "
                + "vitality = ?, physical = ?, popularity = ?, gangName = ?, gangNumber = ?, gangValue = ?, sectValue = ?, "
                + "potential = ?, experience = ?, maxHp = ?, maxMp = ?, rage = ?, cash = ?, fund = ?, deposit = ?, "
                + "maxPetValue = ?, status = ? WHERE id = ?";
        Object[] params = {
                role.getCountId(),
                role.getServerId(),
                role.getRoleName(),
                role.getRoleId(),
                role.getRaceId(),
                role.getSects(),
                role.getLevel(),
                role.getVitality(),
                role.getPhysical(),
                role.getPopularity(),
                role.getGangName(),
                role.getGangNumber(),
                role.getGangValue(),
                role.getSectValue(),
                role.getPotential(),
                role.getExperience(),
                role.getMaxHp(),
                role.getMaxMp(),
                role.getRage(),
                role.getCash(),
                role.getFund(),
                role.getDeposit(),
                role.getMaxPetValue(),
                role.getStatus(),
                role.getId()
        };
        dbHelper.executeUpdate(sql, params);
    }

    // Delete a Role record by ID
    public void deleteById(int id) {
        String sql = "DELETE FROM role WHERE id = ?";
        Object[] params = {id};
        dbHelper.executeUpdate(sql, params);
    }

    // Find Role by ID
    public Role findById(int id) {
        String sql = "SELECT * FROM role WHERE id = ?";
        Object[] params = {id};
        List<Role> roleList = dbHelper.executeQuery(sql, params, Role.class);
        if (roleList.isEmpty()) return null;
        return roleList.get(0);
    }

    public Role findByRoleId(String roleId) {
        String sql = "SELECT * FROM role WHERE roleId = ?";
        Object[] params = {roleId};
        List<Role> roleList = dbHelper.executeQuery(sql, params, Role.class);
        if (roleList.isEmpty()) return null;
        return roleList.get(0);
    }

    // Get all Roles
    public List<Role> findAll() {
        String sql = "SELECT * FROM role";
        Object[] params = {};
        return dbHelper.executeQuery(sql, params, Role.class);
    }

    // Paginated query for Roles
    public Map<String, Object> findAllWithPagination(int pageNum, int pageSize) {
        int offset = (pageNum - 1) * pageSize;

        String sql = "SELECT * FROM role LIMIT ? OFFSET ?";
        Object[] params = {pageSize, offset};
        List<Role> roleList = dbHelper.executeQuery(sql, params, Role.class);

        String countSql = "SELECT COUNT(*) FROM role";
        Object[] countParams = {};
        List<Integer> totalRecordsList = dbHelper.executeQuery(countSql, countParams, Integer.class);
        int totalRecords = totalRecordsList.isEmpty() ? 0 : totalRecordsList.get(0);

        int totalPages = (int) Math.ceil((double) totalRecords / pageSize);

        Map<String, Object> result = new HashMap<>();
        result.put("roleList", roleList);
        result.put("totalRecords", totalRecords);
        result.put("totalPages", totalPages);
        result.put("currentPage", pageNum);

        return result;
    }

    // Clear all Role data
    public void clearData() {
        dbHelper.clearData("role");
    }

    // Drop the Role table
    public void dropTable() {
        dbHelper.dropTable("role");
    }
}
